﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Npgsql;
using NpgsqlTypes;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.EnterpriseServices;
using System.IO;

namespace Tuto.DAL
{
    /// <summary>
    ///     The principal class of the DAL.
    ///     Every classes in the DAL inherit from this class.
    /// </summary>
    public class DALObject 
    {
        private static NpgsqlConnection connection = null ;
        private static string _connectionString = String.Empty;

        private static Object conLocker = new Object();

        public static string connectionStrng
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        /// <summary>
        ///     Create the connection object using a kind of singleton pattern
        /// </summary>
        public static void createConnection()
        {
            lock (conLocker)
            {
                if (connectionStrng != String.Empty)
                {
                    if (connection == null)
                        connection = new NpgsqlConnection(connectionStrng);
                }
            }
        }
        
        /// <summary>
        ///     Create a command object with a given query to be executed
        /// </summary>
        /// <param name="sqlString"> The query to be executed </param>
        /// <returns> The created command </returns>
        public static NpgsqlCommand createCommand(string sqlString)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            NpgsqlCommand command = new NpgsqlCommand(sqlString, connection);
            command.CommandType = CommandType.Text;

            return command;
        }

        /// <summary>
        ///  Create a command propared for stored procedure execution
        /// </summary>
        /// <param name="procedure_name"> The name of the stored procedure </param>
        /// <param name="parameters"> The parameters of the stored procedure </param>
        /// <returns> The prepared NpgsqlCommand </returns>
        public static NpgsqlCommand createCommand(string procedure_name, IDataParameter[] parameters)
        {
            // Creating the command
            if (connection.State != ConnectionState.Open)
                connection.Open();

            NpgsqlCommand command = new NpgsqlCommand(procedure_name,connection);
            command.CommandType = CommandType.StoredProcedure;
            
            // Filling the stored procedure's parameters
            foreach( NpgsqlParameter param in parameters)
            {
                command.Parameters.Add(param);
            }

            // Adding the return value parameter
            command.Parameters.Add( new NpgsqlParameter(
                                                            "returnValue",
                                                            NpgsqlTypes.NpgsqlDbType.Integer,
                                                            4,
                                                            String.Empty,
                                                            ParameterDirection.ReturnValue,
                                                            false,
                                                            0,
                                                            0,
                                                            DataRowVersion.Default,
                                                            null
                                                        )
                                    );
            return command;
        }
        /// <summary>
        /// Execute a stored procedure
        /// </summary>
        /// <param name="procedure_name"> The name of the stored procedure to be executed </param>
        /// <param name="parameters"> The parameters of the stored procedure </param>
        /// <returns> The return value of the stored procedure </returns>
        public static int runSP(string procedure_name, IDataParameter[] parameters)
        {
            int result;

            NpgsqlCommand command = createCommand(procedure_name, parameters);

            // Execution
            command.ExecuteNonQuery();

            // Getting the return value
            result = (int)command.Parameters["returnValue"].Value;

            connection.Clone();

            return result;
        }

        /// <summary>
        /// Execute a stored procedure and store the result in a DataSet
        /// </summary>
        /// <param name="procedure_name"> The name of the stored procedure to be executed </param>
        /// <param name="parameters"> The parameters of the stored procedure </param>
        /// <param name="dataSet"> The DataSet to be filled in </param>
        /// <returns> The return value of the stored procedure </returns>
        
        protected int runSP(string procedure_name, IDataParameter[] parameters, DataSet dataSet)
        {
            int result;
            NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();

            // Preparation and execution
            dataAdapter.SelectCommand = createCommand(procedure_name, parameters);
            dataAdapter.Fill(dataSet, "SourceTable");
            
            //Getting the return value
            result = (int)dataAdapter.SelectCommand.Parameters["returnValue"].Value;

            connection.Close();
            return result;
        }

        /// <summary>
        /// Execute a stored procedure and store the result in a DataSet
        /// </summary>
        /// <param name="procedure_name"> The name of the stored procedure to be executed </param>
        /// <param name="parameters"> The parameters of the stored procedure </param>
        /// <param name="dataSet"> The DataSet to be filled in </param>
        /// <param name="tableName"> The name of DataSet table </param>
        /// <returns> The return value of the stored procedure </returns>
        public static int runSP(string procedure_name, IDataParameter[] parameters, DataSet dataSet, string tableName)
        {
            int result;
            NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter();

            //Preparation and execution
            dataAdapter.SelectCommand = createCommand(procedure_name, parameters);
            dataAdapter.Fill(dataSet, tableName);

            //Getting the return value
            result = (int)dataAdapter.SelectCommand.Parameters["returnValue"].Value;

            connection.Close();

            return result;
        }


        /// <summary>
        ///     Construct and execute an SQL Insert query
        /// </summary>
        /// <param name="tablename"> The name of the table </param>
        /// <param name="fields"> The fields of the new row to be inserted </param>
        /// <returns> Returns the ID of the new record </returns>
        public static int runSQLInsert(string tablename, Dictionary<string, object> fields)
        {
            int i = 0;
            // Construting the  SQL query 
            string[] columns = new string[fields.Count];
            string sqlString = " INSERT INTO " + tablename + " (";
            
            foreach (string col_name in fields.Keys)
            {
                sqlString = sqlString + " " + col_name;
                i++;
                if (i < fields.Count) sqlString = sqlString + ",";
                else
                {
                    sqlString = sqlString + ", date_creation, date_modification ) ";
                }
            }

            sqlString = sqlString + " VALUES (";
            i = 0;
            foreach (var value in fields.Values)
            {
                sqlString = sqlString + " '" + value + "'";
                i++;
                if (i < fields.Count) sqlString = sqlString + ",";
                else
                {
                    sqlString = sqlString + ",'" + DateTime.Now + "','" + DateTime.Now + "')";
                }
            }
            
            int id;
            try
            {
                
                NpgsqlCommand cmd = createCommand(sqlString);
                // Executing the insert Query
                id = cmd.ExecuteNonQuery();
                // Getting the ID of the new row
                cmd.CommandText = "SELECT id FROM " + tablename + " ORDER BY id DESC LIMIT 1";
                NpgsqlDataReader dReader = cmd.ExecuteReader();

                while (dReader.Read())
                    id = int.Parse(dReader[0].ToString());
                dReader.Close();
                // to be continued 
                connection.Clone();
            }
            catch (Exception e)
            {
                throw new Exception(">>> SQL Insert : Error while executing INSERT query ." + "\n >> Querry : " + sqlString + "\n >> Message : " +  e.Message);
            }

            return id;
        }

        /// <summary>
        ///     Construct and execute and SQL Select Query
        /// </summary>
        /// <param name="tables"> The name of the table </param>
        /// <param name="columns"> The columns to select </param>
        /// <param name="constrainte"> The constraint for the WHERE close </param>
        /// <param name="num_operators"> Operators for comparison </param>
        /// <param name="log_operator"> Operators between constrainte </param>
        /// <returns> Returns a DataSet, the result of the query </returns>
        public static DataSet runSQLSelect(List<string> tables, List<string> columns, Dictionary<string, object> constrainte, List<string> num_operators, List<string> log_operator)
        {
            DataSet result = new DataSet();

            string sqlString = " SELECT";
            int i = 0;

            if (columns.Count == 0) sqlString = sqlString + " *";
            else
            {
                foreach (string col in columns)
                {
                    sqlString = sqlString + " " + col;
                    i++;
                    if (i < columns.Count) sqlString = sqlString + ",";
                }
            }
            sqlString = sqlString + " FROM ";
            i = 0;
            foreach (string tablename in tables)
            {
                sqlString = sqlString + " " + tablename;
                i++;
                if (i < tables.Count) sqlString = sqlString + ",";
            }
            string whereString = String.Empty;
            if (constrainte.Count != 0)
                whereString = whereClose(constrainte, num_operators, log_operator);
            sqlString = sqlString + whereString + " ;";

            try
            {
                connection.Open();
                NpgsqlDataAdapter data = new NpgsqlDataAdapter(sqlString, connection);
                data.Fill(result);
                connection.Close();
            }
            catch (Exception e)
            {
                throw new Exception(" >>> SQL Select : Error while executing INSERT query ");
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="fields"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int runSQLUpdate(string tablename, Dictionary<string, object> fields, int id)
        {
            string sqlString = "UPDATE " + tablename + " SET";
            int i = 0;
            int result;
            foreach (string field in fields.Keys)
            {
                sqlString = sqlString + " " + field + " = '" + fields[field] + "'";
                i++;
                if (i < fields.Count) sqlString = sqlString + ",";
            }
            sqlString = sqlString + ", date_modification = '" + DateTime.Now + "'";
            sqlString = sqlString + " WHERE id = '" + id + "';";

            try
            {
                NpgsqlCommand cmd = createCommand(sqlString);

                result = cmd.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception e)
            {
                throw new Exception(">>> SQL Update : Error while executing UPDATE query. " + "\n >> Query : " + sqlString + "\n >> Message : " + e.Message);
            }

            return result;
        }

        /// <summary>
        ///     Update rows
        /// </summary>
        /// <param name="tablename"> The name of the table </param>
        /// <param name="fields"> The fiels to be updated </param>
        /// <param name="constrainte"> The conditions for update </param>
        /// <param name="num_operators"> The operators for conditions </param>
        /// <param name="log_operator"> Logical operators between conditions </param>
        /// <returns> Returns the number of rows updated </returns>
        public static int runSQLUpdate(string tablename, Dictionary<string, object> fields, Dictionary<string, object> constrainte,List<string> num_operators = null, List<string> log_operator = null)
        {
            string sqlString = "UPDATE " + tablename + " SET";
            int i = 0;
            int result;
            foreach (string field in fields.Keys)
            {
                sqlString = sqlString + " " + field + " = '" + fields[field] + "'";
                i++;
                if (i < fields.Count) sqlString = sqlString + ",";

            }

            if (num_operators != null && log_operator != null)
            {
                string whereString = String.Empty;
                if (constrainte.Count != 0)
                    whereString = whereClose(constrainte, num_operators, log_operator);
                sqlString = sqlString + whereString + " ;";
            }
            

            try
            {
                connection.Open();
                NpgsqlCommand cmd = new NpgsqlCommand(sqlString, connection);

                result = cmd.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception e)
            {
                throw new Exception(">>> SQL Update : Error while executing UPDATE query. " + sqlString + e.Message);
            }

            return result;
        }

        /// <summary>
        ///     Delete rows from the table
        /// </summary>
        /// <param name="tablename"> The name of the table </param>
        /// <param name="constrainte"> The constrainte for deletion </param>
        /// <param name="num_operators"> Operators for conditions </param>
        /// <param name="log_operator"> Logicial operators beween conditions </param>
        /// <returns></returns>
        public static int runSQLDelete(string tablename, Dictionary<string, object> constrainte, List<string> num_operators, List<string> log_operator)
        {
            int result;
            string sqlString = "DELETE FROM " + tablename;
            string whereString = String.Empty;
            if (constrainte.Count != 0)
                whereString = whereClose(constrainte, num_operators, log_operator);
            sqlString = sqlString + whereString + " ;";

            try
            {
                connection.Open();

                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sqlString;
                result = cmd.ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception e)
            {
                throw new Exception(">>> SQL Delete : Error while executing DELETE query. ");
            }

            return result;
        }

        /// <summary>
        ///     Execute an SQL file 
        /// </summary>
        /// <param name="sqlFile"> The path to the file to be exectuted </param>
        /// <returns> Returns a bool if execution excessed or not </returns>
        public static bool runSQLFile(string sqlFile)
        {
            bool result = false;

            StreamReader sqlFileReader = new StreamReader(sqlFile);
            string sqlString = String.Empty;
            try
            {
                sqlString = sqlFileReader.ReadToEnd();

                connection.Open();
                NpgsqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sqlString;
                cmd.ExecuteNonQuery();
                connection.Close();
                result = true;
            }
            catch (Exception e)
            {
                throw new Exception(">>> SQL Error : Error while creating tables and constraintes , updating relationships. " + e.Message);
            }

            return result;
        }
        /// <summary>
        ///     Construct the where close for queries
        /// </summary>
        /// <param name="constrainte"> The constraints parameters </param>
        /// <param name="num_operators"> The operators of the conditions </param>
        /// <param name="log_operator"> The logical operators between conditions</param>
        /// <returns> The the where close of the query </returns>
        private static string whereClose(Dictionary<string, object> constrainte, List<string> num_operators, List<string> log_operator)
        {
            string whereString = String.Empty;
            // gestion des null ... to be continued...
            if (num_operators.Count != log_operator.Count + 1)
                throw new Exception(" >>> Incorrect number of operators.");
            else
            {
                whereString = whereString + " WHERE";

                int i = 0;
                int j = 0;
                foreach (var field in constrainte.Keys)
                {
                    if (i < num_operators.Count)
                        whereString = whereString + " " + field + " " + num_operators[i] + " '" + constrainte[field] + "'";
                    /*if (i == num_operators.Count - 1)
                        whereString = whereString + ";";
                    */
                    i++;
                    if (j < log_operator.Count)
                        whereString = whereString + " " + log_operator[j].ToUpper();
                    j++;
                }
            }

            return whereString;
        }

        /// <summary>
        ///     Construct the group by close for queries
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static string groupByClose(List<string> columns)
        {
            throw new Exception(">>> Implementation Error : Group By close is not implemented ");
        }

        /// <summary>
        ///     Constructs the having close for queries
        /// </summary>
        /// <param name="having_conditions"></param>
        /// <returns></returns>
        public static string havingClose(Dictionary<string, object> having_conditions)
        {
            throw new Exception(">>> Implementation Error : Having close is not implemented ");
        }

        /*
          This is just for testing. But the idea is to make it very complete. 
          This will be transformed to  Classes : 
                - one class for SELECT queries,
                - one classe for DELETE queries,
                - one class for UPDATE queries,
                - one classe for INSERT queries, and others.
         */
/*
         
        /// <summary>
        /// Customized ServicedComponent method. Set the database connection string
        /// </summary>
        /// <param name="constructionString"> Sent String to the object from COM+ as construction string</param>
        public override void Construct(string constructionString)
        {
            _connectionString = constructionString;
        }

        /// <summary>
        /// Customized ServicedComponent method. 
        /// Called when COM+ activate this component in JIT.
        /// Instanciate de connection the the database 
        /// </summary>
        public override void Activate()
        {
            connection = new NpgsqlConnection(_connectionString);
        }

        /// <summary>
        /// Customized ServicedComponent method. 
        /// Called when COM+ disactivate this component in JIT.
        /// Free de connection the the database 
        /// </summary>
        public override void Deactivate()
        {
            connection = null;
        }
*/
    }
}